import xlsxwriter
from io import BytesIO
from apis import api_routes
from sqlalchemy import text
from typing import List
from typing import Union
from urllib.parse import unquote, quote
from security.decoration import certification
from flask import g, request, jsonify, Response, abort
from public.local import local_time
from models.facility.facility import Facility
from datetime import datetime, timedelta
from models.owner.owner import Owner


@api_routes.route("/awaits", methods=["GET"])
@certification(auth=True, role=None)
def func_awaits_list_get_api():
    """
    设备待机记录查询, 只要有token 都可以访问, API 开发完成，对接PLC未完成 完成API开发
    组合方式, 一共4种，其他组合默认第一种
        /api/awaits?index=1&size=10
        /api/awaits?index=1&size=10&facility_id=1
        /api/awaits?index=1&size=10&facility_id=1&start=2023-11-03&finish=2023-11-04
        /api/awaits?index=1&size=10&start=2023-11-03&finish=2023-11-04
    http://127.0.0.1:8080/api/awaits
    Content-Type=application/x-www-form-urlencoded
    methods=["GET"]
    {
    "index"         int      ****  第几页，默认为1
    "size"          int      ****  每页数据多少, 默认每页10条数据
    "facility_id"   int            设备ID
    "start"         string         开始日期
    "finish"        string         结束日期
    "owner_id"      Int         默认(下拉，键值对)，可以不选, 排班名称，1白班， 2晚班
    }
    :return:
        {'status': 400, 'data': data}
    ---
    tags:
      - 设备统计待机
    """
    index = request.args.get("index", default=1, type=int)
    size = request.args.get("size", default=10, type=int)
    ft_id: int = request.args.get("facility_id", default=None, type=int)
    start: Union[str, datetime, None] = request.args.get("start", default=None, type=str)
    finish: Union[str, datetime, None] = request.args.get("finish", default=None, type=str)
    owner_id: int = request.args.get("owner_id", default=0, type=int)

    if owner_id not in [0, 1, 2]:
        abort(code=400)

    if start is not None and len(start) == 0:
        start = None
    if finish is not None and len(finish) == 0:
        finish = None

    if (owner_id and start is None) or (owner_id and finish is None) or (owner_id and start != finish):
        return jsonify({'status': 4001, 'msg': "选择班次不能跨天查询或未指定日期"})

    back_start = start
    back_finish = finish

    if start is not None:
        start = datetime.strptime(start, "%Y-%m-%d").strftime("%Y-%m-%d %H:%M:%S")
    if finish is not None:
        temp_date_time = datetime.strptime(finish, "%Y-%m-%d") + timedelta(days=1) - timedelta(seconds=1)
        finish = temp_date_time.strftime("%Y-%m-%d %H:%M:%S")

    db_owner: Union[Owner, None] = g.session.query(Owner).filter(Owner.id == owner_id).first()
    if db_owner is not None and db_owner.start <= db_owner.finish:
        start = "{} {}".format(back_start, db_owner.start.strftime("%H:%M:%S"))
        finish = "{} {}".format(back_finish, db_owner.finish.strftime("%H:%M:%S"))
    elif db_owner is not None and db_owner.start > db_owner.finish:
        start = "{} {}".format(back_start, db_owner.start.strftime("%H:%M:%S"))
        hour = db_owner.finish.hour
        temp_date_time = datetime.strptime(back_finish, "%Y-%m-%d") + timedelta(days=1) + timedelta(hours=hour)
        finish = temp_date_time.strftime("%Y-%m-%d %H:%M:%S")

    res: list = []
    sql_count = """
        SELECT 
            COUNT(awaits.id)
        FROM awaits INNER JOIN facility ON awaits.facility_id = facility.id {}
    """
    # sql_date = """
    #     SELECT
    #         facility.name, facility.workplace,
    #         coalesce(to_char(awaits.time,'YYYY-MM-DD hh24:mi:ss'), null) as start,
    #         coalesce(to_char(awaits.finish,'YYYY-MM-DD hh24:mi:ss'), null) as finish,
    #         awaits.account, awaits.expenditure
    #     FROM awaits INNER JOIN facility ON
    #     awaits.facility_id = facility.id {} ORDER BY awaits.id LIMIT :arg1 OFFSET :arg2
    # """
    sql_date = """
        SELECT
            facility.name, facility.workplace,
            coalesce(to_char(detailed_fst.time,'YYYY-MM-DD hh24:mi:ss'), null) as start,
            coalesce(to_char(detailed_fst.finish,'YYYY-MM-DD hh24:mi:ss'), null) as finish,
            detailed_fst.account, detailed_fst.expenditure
        FROM
        (
            SELECT
                awaits.time, awaits.finish, awaits.account, 
                awaits.expenditure, awaits.id, awaits.facility_id
            FROM awaits {} ORDER BY awaits.id LIMIT :arg1 OFFSET :arg2
        ) AS detailed_fst
        INNER JOIN facility ON 
        detailed_fst.facility_id = facility.id ORDER BY detailed_fst.id 
    """
    change: bool = False
    if ft_id is not None and start is None and finish is None:
        change = True
        sql_count = sql_count.format("WHERE awaits.facility_id = {}".format(ft_id))
        sql_date = sql_date.format("WHERE awaits.facility_id = {}".format(ft_id))
    elif ft_id is not None and start is not None and finish is not None:
        change = True
        temp: str = "WHERE awaits.facility_id = {} AND awaits.time > '{}' AND awaits.time < '{}'"
        sql_count = sql_count.format(temp .format(ft_id, start, finish))
        sql_date = sql_date.format(temp.format(ft_id, start, finish))
    elif ft_id is None and start is not None and finish is not None:
        change = True
        temp: str = "WHERE awaits.time > '{}' AND awaits.time < '{}'"
        sql_count = sql_count.format(temp.format(start, finish))
        sql_date = sql_date.format(temp.format(start, finish))
    if not change:
        sql_count = sql_count.format("")
        sql_date = sql_date.format("")
    counts, = g.session.execute(text(sql_count)).fetchone()
    page = int((counts / size) + 1) if (counts / size) > int(counts / size) else int(counts / size)

    data = g.session.execute(text(sql_date), {"arg1": size, "arg2": ((index - 1) * size), "fid": ft_id}).fetchall()

    for itr in data:
        res.append({
            "facility_name": itr[0], "workplace": itr[1],
            "time": itr[2], "finish": itr[3], "account": itr[4], "expenditure": itr[5]
        })
    doc: dict = {
        "time": "开始时间", "finish": "结束时间", "expenditure": "待机时间/秒",
        "account": "操作用户", "workplace": "设备位置", "facility_name": "设备名字"
    }
    return jsonify({'status': 200, 'data': res, "index": index, "size": size, "total": counts, "pages": page, "doc": doc})


@api_routes.route("/awaits/output", methods=["GET"])
def func_awaits_list_output_get_api():
    """
    设备待机录导出EXCEL API, 完成API开发
    组合方式, 一共4种，其他组合默认第一种
        /api/awaits/output
        /api/awaits/output?facility_id=1
        /api/awaits/output?facility_id=1&start=2023-11-03&finish=2023-11-04
        /api/awaits/output?start=2023-11-03&finish=2023-11-04
    http://127.0.0.1:8080/api/awaits/output
    Content-Type=application/x-www-form-urlencoded
    methods=["GET"]
    {
    "file"          string         导出文件名，没有默认按照今天日期
    "facility_id"   int            设备ID
    "start"         string         开始日期
    "finish"        string         结束日期
    "owner_id"      Int            默认(下拉，键值对)，可以不选, 排班名称，1白班， 2晚班
    }
    :return:
        数据流
    ---
    tags:
      - 设备统计待机
    """
    start: Union[str, datetime, None] = request.args.get("start", default=None, type=str)
    finish: Union[str, datetime, None] = request.args.get("finish", default=None, type=str)
    file: Union[str, None] = request.args.get("file", default=None, type=str)
    ft_id: Union[int, None] = request.args.get("facility_id", default=None, type=int)
    owner_id: int = request.args.get("owner_id", default=0, type=int)

    if owner_id not in [0, 1, 2]:
        abort(code=400)

    if start is not None and len(start) == 0:
        start = None
    if finish is not None and len(finish) == 0:
        finish = None

    if (owner_id and start is None) or (owner_id and finish is None) or (owner_id and start != finish):
        return Response()

    back_start = start
    back_finish = finish

    if start is not None:
        start = datetime.strptime(start, "%Y-%m-%d").strftime("%Y-%m-%d %H:%M:%S")
    if finish is not None:
        temp_date_time = datetime.strptime(finish, "%Y-%m-%d") + timedelta(days=1) - timedelta(seconds=1)
        finish = temp_date_time.strftime("%Y-%m-%d %H:%M:%S")

    db_owner: Union[Owner, None] = g.session.query(Owner).filter(Owner.id == owner_id).first()
    if db_owner is not None and db_owner.start <= db_owner.finish:
        start = "{} {}".format(back_start, db_owner.start.strftime("%H:%M:%S"))
        finish = "{} {}".format(back_finish, db_owner.finish.strftime("%H:%M:%S"))
    elif db_owner is not None and db_owner.start > db_owner.finish:
        start = "{} {}".format(back_start, db_owner.start.strftime("%H:%M:%S"))
        hour = db_owner.finish.hour
        temp_date_time = datetime.strptime(back_finish, "%Y-%m-%d") + timedelta(days=1) + timedelta(hours=hour)
        finish = temp_date_time.strftime("%Y-%m-%d %H:%M:%S")

    sql_date = """
        SELECT 
            facility.name, facility.workplace,
            coalesce(to_char(awaits.time,'YYYY-MM-DD hh24:mi:ss'), null) as start,
            coalesce(to_char(awaits.finish,'YYYY-MM-DD hh24:mi:ss'), null) as finish,
            awaits.account, awaits.expenditure
        FROM awaits INNER JOIN facility ON awaits.facility_id = facility.id {} ORDER BY awaits.id 
    """
    change: bool = False
    if ft_id is not None and start is None and finish is None:
        change = True
        sql_date = sql_date.format("WHERE facility.id = {}".format(ft_id))
    elif ft_id is not None and start is not None and finish is not None:
        change = True
        temp: str = "WHERE facility.id = {} AND awaits.time > '{}' AND awaits.time < '{}'"
        sql_date = sql_date.format(temp.format(ft_id, start, finish))
    elif ft_id is None and start is not None and finish is not None:
        change = True
        temp: str = "WHERE awaits.time > '{}' AND awaits.time < '{}'"
        sql_date = sql_date.format(temp.format(start, finish))
    if not change:
        sql_date = sql_date.format("")

    file_name = "{}.xlsx".format(local_time())
    if file is not None:
        file_name = "{}.xlsx".format(file)
    data = g.session.execute(text(sql_date)).fetchall()

    output = BytesIO()
    workbook = xlsxwriter.Workbook(output, {'in_memory': True})
    worksheet_index = workbook.add_worksheet("设备历史报警数据表")
    worksheet_index.set_column("A:ZZ", 32)
    message: list = ["设备名字", "设备位置", "开始时间", "结束时间", "操作用户", "待机时间/秒"]
    worksheet_index.write_row(row=0, col=0, data=message)
    for index, itr in enumerate(data):
        sequence = index + 1 + 0
        worksheet_index.write_row(row=sequence, col=0, data=list(itr))

    # 在数据流之前关闭工作薄
    workbook.close()
    output.seek(0)
    resp = Response(output, content_type='application/octet-stream')
    resp.headers["Content-Disposition"] = "attachment; filename={0}; filename*=utf-8''{0}".format(quote(file_name))
    resp.headers.set("Access-Control-Expose-Headers", "*")
    return resp, 200


@api_routes.route("/awaits/statistics", methods=["GET"])
@certification(auth=True, role=None)
def func_awaits_statistics_get_api():
    """
    设备待机统计报表, 完成API开发
    组合方式, 一共4种，其他组合默认第一种
        /api/awaits/statistics
        /api/awaits/statistics?facility_id=1
        /api/awaits/statistics?facility_id=1&start=2023-11-03&finish=2023-11-04
        /api/awaits/statistics?start=2023-11-03&finish=2023-11-04
    http://127.0.0.1:8080/api/awaits/statistics
    Content-Type=application/x-www-form-urlencoded
    methods=["GET"]
    {
    "facility_id"   int            设备ID
    "start"         string         开始日期
    "finish"        string         结束日期
    }
    :return:
        数据流
    ---
    tags:
      - 设备统计待机
    """
    start: Union[str, datetime, None] = request.args.get("start", default=None, type=str)
    finish: Union[str, datetime, None] = request.args.get("finish", default=None, type=str)
    ft_id: Union[int, None] = request.args.get("facility_id", default=None, type=int)
    owner_id: int = request.args.get("owner_id", default=0, type=int)

    if owner_id not in [0, 1, 2]:
        abort(code=400)

    if start is not None and len(start) == 0:
        start = None
    if finish is not None and len(finish) == 0:
        finish = None

    if (owner_id and start is None) or (owner_id and finish is None) or (owner_id and start != finish):
        return jsonify({'status': 4001, 'msg': "选择班次不能跨天查询或未指定日期"})

    back_start = start
    back_finish = finish

    if start is not None:
        start = datetime.strptime(start, "%Y-%m-%d").strftime("%Y-%m-%d %H:%M:%S")
    if finish is not None:
        temp_date_time = datetime.strptime(finish, "%Y-%m-%d") + timedelta(days=1) - timedelta(seconds=1)
        finish = temp_date_time.strftime("%Y-%m-%d %H:%M:%S")

    db_owner: Union[Owner, None] = g.session.query(Owner).filter(Owner.id == owner_id).first()
    if db_owner is not None and db_owner.start <= db_owner.finish:
        start = "{} {}".format(back_start, db_owner.start.strftime("%H:%M:%S"))
        finish = "{} {}".format(back_finish, db_owner.finish.strftime("%H:%M:%S"))
    elif db_owner is not None and db_owner.start > db_owner.finish:
        start = "{} {}".format(back_start, db_owner.start.strftime("%H:%M:%S"))
        hour = db_owner.finish.hour
        temp_date_time = datetime.strptime(back_finish, "%Y-%m-%d") + timedelta(days=1) + timedelta(hours=hour)
        finish = temp_date_time.strftime("%Y-%m-%d %H:%M:%S")

    sql_date = """
        SELECT
            facility.name, facility.workplace,
            facility.serials, awaits_st.numbers, awaits_st.totals
        FROM
        (
            SELECT
                facility_id, COUNT(facility_id) AS numbers, SUM(expenditure) AS totals
            FROM awaits {} GROUP BY facility_id
        ) AS awaits_st
        INNER JOIN facility ON awaits_st.facility_id = facility.id ORDER BY facility.id
    """
    change = False
    if ft_id is not None and start is None and finish is None:
        change: bool = True
        sql_date = sql_date.format("WHERE facility_id = {}".format(ft_id))
    elif ft_id is not None and start is not None and finish is not None:
        change: bool = True
        temp: str = "WHERE facility_id = {} AND time > '{}' AND time < '{}'"
        sql_date = sql_date.format(temp.format(ft_id, start, finish))
    elif ft_id is None and start is not None and finish is not None:
        change: bool = True
        temp: str = "WHERE time > '{}' AND time < '{}'"
        sql_date = sql_date.format(temp.format(start, finish))
    if not change:
        sql_date = sql_date.format("")
    db_data = g.session.execute(text(sql_date)).fetchall()

    data: list = []
    parm: Union[str, None] = None
    db_fts: List[Facility] = g.session.query(Facility).filter(Facility.uuid4 != parm).order_by(Facility.id).all()
    for index0, itr0 in enumerate(db_fts):
        init_awaits_numbers: int = 0
        init_awaits_totals: int = 0
        for ft_nm, ft_wk, ft_s2, as_ns, as_ts in db_data:
            if ft_s2 == itr0.serials:
                init_awaits_numbers = as_ns
                init_awaits_totals = as_ts
                break
        if init_awaits_totals is None:
            init_awaits_totals = 0
            
        hour_format = int(init_awaits_totals / 3600)
        mount_format = int((init_awaits_totals - (3600 * hour_format)) / 60)
        se_format = int(init_awaits_totals - (3600 * hour_format) - (60 * mount_format))
        time_format = "{}时{}分{}秒".format(hour_format, mount_format, se_format)

        data.append({
            "facility_name": itr0.name,
            "facility_workplace": itr0.workplace,
            "facility_serials": itr0.serials,
            "awaits_numbers": init_awaits_numbers,
            "awaits_totals": init_awaits_totals,
            "time_format": time_format
        })
    docs: dict = {
        "facility_name": "设备名称",
        "facility_workplace": "设备位置",
        "facility_serials": "设备序列号",
        "awaits_numbers": "发生次数",
        "awaits_totals": "时间/单位秒",
        "time_format": "秒转时间格式"
    }
    return jsonify({'status': 200, 'data': data, "docs": docs})
